﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using WPFDBBrowser.Interfaces;
using WPFDBBrowser.Interfaces.DBTreeView;

namespace WPFDBBrowser.MSAccessFactory
{
    public class MSAccessDBFactory : IDBFactory
    {
        #region Const
        private const string DT_YESNO = "Yes/No";
        private const string DT_NUMBER = "Number";
        private const string DT_TEXT = "Text";
        private const string DT_MEMO = "Memo";
        private const string DT_DATETIME = "Date/Time";
        private const string DT_CURRENCY = "Currency";
        private const string DT_AUTONUMBER = "Autonumber";
        private const string DT_OLE_OBJECT = "OLE Object";
        private const string DT_HYPERLINK = "Hyperlink";
        private const string DT_ATTACHMENT = "Attachment";

        private const string NUMBER_BYTE = "Byte";
        private const string NUMBER_INT = "Integer";
        private const string NUMBER_LONG = "Long Integer";
        private const string NUMBER_SINGLE = "Single";
        private const string NUMBER_DOUBLE = "Double";
        private const string NUMBER_REPLICATION_ID = "Replication ID";
        private const string NUMBER_DECIMAL = "Decimal"; 
	    #endregion

        #region IDBFactory Members
        
        #region Properties

        public string FriendlyName
        {
            get { return "MS Access Provider"; }
        }

        public string Name
        {
            get { return this.GetType().FullName; }
        }

        public string Type
        {
            get { return "WPFDBBrowser.MSAccessFactory.MSAccessDBFactory"; }
        }

        public string Description
        {
            get { return "MS Access Provider for WPFDBBrowser application"; }
        }

        #endregion

        #region Methods

        public System.Data.Common.DbConnectionStringBuilder GetConnectionStringBuilder(string connectionString)
        {
            return new System.Data.OleDb.OleDbConnectionStringBuilder(connectionString);
        }

        public bool CheckConnection(string connectionString)
        {
            // try to open connection
            using (System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(connectionString))
            {
                con.Open();
            }
            return true;
        }

        public IEnumerable<ITable> GetTables(string connectionString)
        {
            List<ITable> retColl = new List<ITable>();
            using (System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(connectionString))
            {
                con.Open();

                ////Uncomment if you want to get metadata
                //DataTable metadata = con.GetSchema(System.Data.OleDb.OleDbMetaDataCollectionNames.Tables);
                ////metadata.WriteXmlSchema(@"D:\OracleSchema.xml");
                //metadata.WriteXml(@"D:\Schema.xml", XmlWriteMode.WriteSchema);

                System.Data.DataTable tables = con.GetSchema(System.Data.OleDb.OleDbMetaDataCollectionNames.Tables, new string[] { null, null, null, "TABLE" });
                foreach (DataRow item in tables.Rows)
                {
                    retColl.Add(new DBTableEntity(this.Type) { Name = (string)item["TABLE_NAME"] });
                }
            }

            return retColl;
        }

        public IEnumerable<IView> GetViews(string connectionString)
        {
            IList<IView> listView = new List<IView>();

            using (System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(connectionString))
            {
                con.Open();

                System.Data.DataTable tables = con.GetSchema(System.Data.SqlClient.SqlClientMetaDataCollectionNames.Views);
                foreach (DataRow item in tables.Rows)
                {
                    listView.Add(new DBViewEntity(this.Type)
                    {
                        Query = (string)item["TEXT"],
                        Name = (string)item["VIEW_NAME"]
                    });
                }
            }
            return listView;
        }

        public IEnumerable<IColumn> GetColumns(string connectionString, string tableName)
        {
            List<IColumn> columns = new List<IColumn>();
            using (System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(connectionString))
            {
                con.Open();
                // System.Data.OleDb.OleDbMetaDataCollectionNames.Columns
                DataTable columnsTable = con.GetSchema(System.Data.OleDb.OleDbMetaDataCollectionNames.Columns, new string[] { null, null, tableName });
                //DataTable columnsTable = con.GetSchema(System.Data.OleDb.OleDbMetaDataCollectionNames.Columns);
                //columnsTable.WriteXml(@"D:\TempTableColumns.xml", XmlWriteMode.WriteSchema);
                foreach (DataRow row in columnsTable.Rows)
                {
                    columns.Add(this.ParseColumnRow(row));
                }
            }
            columns.Sort(delegate(IColumn column1, IColumn column2)
            {
                if (column1 != null && column1.Name != null &&
                    column2 != null)
                    return column1.Name.CompareTo(column2.Name);
                return 0;
            });
            return columns;
        }

        public string GetQuery(IColumnContainer tableOrView, int numberOfRows)
        {
            if (tableOrView.Items == null || tableOrView.Items.Count == 0)
                throw new ArgumentNullException("tableOrView.Items");

            string query = string.Format("SELECT {0} FROM {1}", this.GetColumns(tableOrView), tableOrView.Name);
            if (numberOfRows > 0)
                query += string.Format("{0} WHERE {1}", query, GetRowsRestriction(numberOfRows));

            return query;
        }

        public DataTable RunQuery(string connectionString, string query)
        {
            using (System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(connectionString))
            {
                DataTable table = new DataTable();

                System.Data.OleDb.OleDbDataAdapter tableAdapter = new System.Data.OleDb.OleDbDataAdapter(query, con);
                tableAdapter.Fill(table);

                return table;
            }
        }

        public string PrepareViewQuery(IView view, int numberOfRows)
        {
            throw new NotImplementedException();

        }

        #endregion

        #endregion

        #region OVerride Methods

        public override string ToString()
        {
            return this.FriendlyName;
        }

        #endregion

        #region Private Methods

        private string GetRowsRestriction(int numberOfRows)
        {
            return string.Format("ROWNUM <= {0}", numberOfRows);
        }

        private object GetColumns(IColumnContainer tableOrView)
        {
            StringBuilder columns = new StringBuilder();
            foreach (IColumn column in tableOrView.Items)
            {
                if (columns.Length > 0)
                    columns.Append(", ");
                columns.Append(column.Name);
            }

            return columns.ToString();
        }

        private IColumn ParseColumnRow(DataRow row)
        {
            string name = (string)row["COLUMN_NAME"];
            //System.Diagnostics.Debug.WriteLine(name, "Column");
            bool nullable = row["IS_NULLABLE"].ToString() == "Y";
            int typeId = (int)row["DATA_TYPE"];
            string type = string.Empty;
            string format = string.Empty;
            long length = 0;
            short scale = 0;
            long precision = 0;
            switch (typeId)
            {
                case 2:
                    type = DT_NUMBER;
                    format = NUMBER_INT;
                    precision = Convert.ToInt64(row["NUMERIC_PRECISION"]);
                    break;
                case 3:
                    type = DT_NUMBER;
                    // could be also autonumber ...
                    format = NUMBER_LONG;
                    precision = Convert.ToInt64(row["NUMERIC_PRECISION"]);
                    break;
                case 4:
                    type = DT_NUMBER;
                    format = NUMBER_SINGLE;
                    precision = Convert.ToInt64(row["NUMERIC_PRECISION"]);
                    break;
                case 5:
                    type = DT_NUMBER;
                    format = NUMBER_DOUBLE;
                    precision = Convert.ToInt64(row["NUMERIC_PRECISION"]);
                    break;
                case 6:
                    type = DT_CURRENCY;
                    precision = Convert.ToInt64(row["NUMERIC_PRECISION"]);
                    break;
                case 7:
                    type = DT_DATETIME;
                    precision = (long)row["DATETIME_PRECISION"];
                    break;
                // yes/no data type
                case 11:
                    type = DT_YESNO;
                    length = (long)row["CHARACTER_MAXIMUM_LENGTH"];
                    break;
                case 17:
                    type = DT_NUMBER;
                    format = NUMBER_BYTE;
                    precision = Convert.ToInt64(row["NUMERIC_PRECISION"]);
                    break;
                case 72:
                    type = DT_NUMBER;
                    format = NUMBER_REPLICATION_ID;
                    break;
                case 128:
                    type = DT_OLE_OBJECT;
                    break;
                case 130:
                    // it could be also memo or hyperlink or attachment, but now i'm not able to recognize it
                    type = DT_TEXT;
                    length = (long)row["CHARACTER_MAXIMUM_LENGTH"];
                    break;
                case 131:
                    type = DT_NUMBER;
                    format = NUMBER_DECIMAL;
                    precision = Convert.ToInt64(row["NUMERIC_PRECISION"]);
                    scale = (short)row["NUMERIC_SCALE"];
                    break;
                default:
                    type = "Unknown Data Type";
                    break;
            }
            return new DBColumn(type) { Name = name, Description = GetText(type, length, precision, scale, format, nullable) };

        }

        private string GetText(string type, long length, long precision, short scale, string numberFormat, bool nullable)
        {
            string text = string.Empty;
            switch (type)
            {
                #region DT_TEXT
                case DT_TEXT:
                #endregion
                #region DT_YES/NO
                case DT_YESNO:
                    text = string.Format(" ({0}({1}), {2})", type, length, nullable);
                    break;
                #endregion
                #region DT_NUMBER
                case DT_NUMBER:
                    switch (numberFormat)
                    {
                        case NUMBER_BYTE:
                        case NUMBER_INT:
                        case NUMBER_LONG:
                        case NUMBER_SINGLE:
                        case NUMBER_DOUBLE:
                            text = string.Format(" ({0}({1}, {2}), {3})", type, numberFormat, precision, nullable);
                            break;
                        case NUMBER_DECIMAL:
                            text = string.Format(" ({0}({1}, {2}:{3}), {4})", type, numberFormat, precision, scale, nullable);
                            break;
                        case NUMBER_REPLICATION_ID:
                            text = string.Format(" ({0}({1}), {2})", type, numberFormat, nullable);
                            break;
                        default:
                            text = string.Format(" ({0}({1}), {2})", type, numberFormat, nullable);
                            break;
                    }
                    break;
                #endregion
                #region DT_OLE_OBJECT
                case DT_OLE_OBJECT:
                    text = string.Format(" ({0}, {1})", type, nullable);
                    break;
                #endregion
                #region DT_CURRENCY
                case DT_CURRENCY:
                #endregion
                #region DT_DATETIME
                case DT_DATETIME:
                    text = string.Format(" ({0}({1}), {2})", type, precision, nullable);
                    break;
                #endregion

                default:
                    text += " (unknown)";
                    break;
            }
            return text;
        }

        #endregion
    }
}
